/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import models.DBHandler;
import models.beans.FAQ;

/**
 *
 * @author Hoang Dong Tien
 */
public class FAQDAO {

    public static List<FAQ> getAllFAQsByCondition(String whereClause) throws ClassNotFoundException, SQLException {
        List<FAQ> faqs = new ArrayList<FAQ>();
        ResultSet rs = null;
        Connection oconn = DBHandler.getConnection();
        StringBuilder sb = new StringBuilder("SELECT * FROM [FAQs] ");
        if(whereClause != null){
            sb.append(whereClause);
        }
        PreparedStatement pstmt = oconn.prepareStatement(sb.toString());
        rs = pstmt.executeQuery();

        FAQ faqTmp = null;
        while(rs.next()){
            faqTmp = new FAQ();
            faqTmp.setFAQId(rs.getInt("FAQId"));
            faqTmp.setQuestion(rs.getNString("Question"));
            faqTmp.setAnswer(rs.getNString("Answer"));

            faqs.add(faqTmp);
        }

        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);

        return faqs;
    }

    public static int addNewFAQ(FAQ newfaq) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("INSERT INTO [FAQs] ([Question],[Answer])VALUES(?,?)");
        pstmt.setNString(1, newfaq.getQuestion());
        pstmt.setNString(2, newfaq.getAnswer());
        result = pstmt.executeUpdate();

        

        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }

    public static int updateFAQ(FAQ newfaq) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("UPDATE [FAQs] SET [Question]=?,[Answer]=? WHERE [FAQId]=?");
        pstmt.setNString(1, newfaq.getQuestion());
        pstmt.setNString(2, newfaq.getAnswer());
        pstmt.setInt(3, newfaq.getFAQId());
       // System.out.println("LOG -> " + newfaq.getFAQId() +"#" + newfaq.getAnswer() +"#"+ newfaq.getQuestion());
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    
    public static int deleteFAQ(int faqId) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("DELETE FROM [FAQs] WHERE FAQId=?");
        pstmt.setInt(1, faqId);
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    
    public static int deleteFAQ(FAQ faq) throws ClassNotFoundException, SQLException {
        return deleteFAQ(faq.getFAQId());
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        List<FAQ> faqs = getAllFAQsByCondition(null);
        for (FAQ faq : faqs){
            System.out.println("FAQ Name : " + faq.getQuestion() + "\n");
        }
    }
}
